package com.ease.dao.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.LobHandler;

import com.ease.common.vo.Ordering;
import com.ease.common.vo.Pagination;
import com.ease.dao.JdbcTemplateDataAccessObject;
import com.ease.dao.dialect.JdbcTemplateDialect;
import com.ease.dao.jdbctemplate.core.SpringStoredProcedure;
import com.ease.dao.jdbctemplate.core.StoredProcedureParam;
import com.ease.dao.vo.PaginationSqlParameterSource;

/**
 * 
 * @author nathanleewei
 * 
 */
public class JdbcTemplateDataAccessObjectImpl extends AbstractDataAccessObjectImpl implements JdbcTemplateDataAccessObject {

	private static final transient Log log = LogFactory.getLog(JdbcTemplateDataAccessObjectImpl.class);

	private JdbcTemplateDialect jdbcTemplateDialect;

	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	private LobHandler lobHandler;

	public JdbcTemplate getJdbcTemplate() {
		return (JdbcTemplate) this.getNamedParameterJdbcTemplate().getJdbcOperations();
	}

	//
	public long sequence(String sequenceName) {
		return this.findLong(this.jdbcTemplateDialect.sequenceStatement(sequenceName));
	}

	/** create */
	public int create(String namedStatement, SqlParameterSource sqlParameterSource) {
		try {
			return this.getNamedParameterJdbcTemplate().update(namedStatement, sqlParameterSource);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource, e);
			throw e;
		}
	}

	public int create(String namedStatement, Map<String, Object> parameter) {
		try {
			return this.getNamedParameterJdbcTemplate().update(namedStatement, parameter);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", parameter=" + parameter, e);
			throw e;
		}
	}

	/** findInt */
	public int findInt(String namedStatement, SqlParameterSource sqlParameterSource) {
		try {
			return this.getNamedParameterJdbcTemplate().queryForInt(namedStatement, sqlParameterSource);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource, e);
			throw e;
		}
	}

	public int findInt(String namedStatement, Map<String, Object> parameter) {
		try {
			return this.getNamedParameterJdbcTemplate().queryForInt(namedStatement, parameter);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", parameter=" + parameter, e);
			throw e;
		}
	}

	public int findInt(String namedStatement) {
		return this.findInt(namedStatement, (SqlParameterSource) null);
	}

	/** findLong */
	public long findLong(String namedStatement, SqlParameterSource sqlParameterSource) {
		try {
			return this.getNamedParameterJdbcTemplate().queryForLong(namedStatement, sqlParameterSource);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource, e);
			throw e;
		}
	}

	public long findLong(String namedStatement, Map<String, Object> parameter) {
		try {
			return this.getNamedParameterJdbcTemplate().queryForLong(namedStatement, parameter);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", parameter=" + parameter, e);
			throw e;
		}
	}

	public long findLong(String namedStatement) {
		return this.findLong(namedStatement, (SqlParameterSource) null);
	}

	/** findOne */
	public <T> T findOne(String namedStatement, String onlyParameterKey, Object onlyParameterValue, RowMapper<T> rowMapper) {
		Map<String, Object> parameter = onlyParameter(onlyParameterKey, onlyParameterValue);
		return this.findOne(namedStatement, parameter, rowMapper);
	}

	public <T> T findOne(String namedStatement, SqlParameterSource sqlParameterSource, RowMapper<T> rowMapper) {
		try {
			List<T> items = escapeEmpty(this.getNamedParameterJdbcTemplate().query(namedStatement, sqlParameterSource, rowMapper));
			return firstOne(items);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource + ", rowMapper=" + rowMapper, e);
			throw e;
		}
	}

	public <T> T findOne(String namedStatement, Map<String, Object> parameter, RowMapper<T> rowMapper) {
		try {
			List<T> items = escapeEmpty(this.getNamedParameterJdbcTemplate().query(namedStatement, parameter, rowMapper));
			return firstOne(items);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", parameter=" + parameter + ", rowMapper=" + rowMapper, e);
			throw e;
		}
	}

	public <T> T findOne(String namedStatement, Object parameter, RowMapper<T> rowMapper) {
		return this.findOne(namedStatement, safeSqlParameterSource(parameter), rowMapper);
	}

	public <T> T findOne(String namedStatement, RowMapper<T> rowMapper) {
		return this.findOne(namedStatement, (Object) null, rowMapper);
	}

	/** List<T> find */
	public <T> List<T> find(String namedStatement, SqlParameterSource sqlParameterSource, int skipResult, int maxResult, Ordering ordering, RowMapper<T> rowMapper) {
		try {
			sqlParameterSource = new PaginationSqlParameterSource(sqlParameterSource, ordering, skipResult, maxResult);
			return escapeEmpty(this.getNamedParameterJdbcTemplate().query(
					this.jdbcTemplateDialect.paginationStatement(this.jdbcTemplateDialect.appendOrderBy(namedStatement, ordering), skipResult, maxResult), sqlParameterSource, rowMapper));
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource + ", skipResult=" + skipResult + ", maxResult=" + maxResult + ", ordering=" + ordering
					+ ", rowMapper=" + rowMapper, e);
			throw e;
		}
	}

	public <T> List<T> find(String namedStatement, Map<String, Object> parameter, int skipResult, int maxResult, Ordering ordering, RowMapper<T> rowMapper) {
		try {
			SqlParameterSource sqlParameterSource = new PaginationSqlParameterSource(parameter, ordering, skipResult, maxResult);
			return escapeEmpty(this.getNamedParameterJdbcTemplate().query(
					this.jdbcTemplateDialect.paginationStatement(this.jdbcTemplateDialect.appendOrderBy(namedStatement, ordering), skipResult, maxResult), sqlParameterSource, rowMapper));
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", parameter=" + parameter + ", skipResult=" + skipResult + ", maxResult=" + maxResult + ", ordering=" + ordering + ", rowMapper="
					+ rowMapper, e);
			throw e;
		}
	}

	//
	public <T> List<T> find(String namedStatement, String onlyParameterKey, Object onlyParameterValue, Ordering ordering, RowMapper<T> rowMapper) {
		Map<String, Object> parameter = onlyParameter(onlyParameterKey, onlyParameterValue);
		return this.find(namedStatement, parameter, ordering, rowMapper);
	}

	public <T> List<T> find(String namedStatement, SqlParameterSource sqlParameterSource, Ordering ordering, RowMapper<T> rowMapper) {
		return this.find(namedStatement, sqlParameterSource, RAW_SKIP_RESULT, RAW_MAX_RESULT, ordering, rowMapper);
	}

	public <T> List<T> find(String namedStatement, Map<String, Object> parameter, Ordering ordering, RowMapper<T> rowMapper) {
		return this.find(namedStatement, parameter, RAW_SKIP_RESULT, RAW_MAX_RESULT, ordering, rowMapper);
	}

	public <T> List<T> find(String namedStatement, Object parameter, Ordering ordering, RowMapper<T> rowMapper) {
		return this.find(namedStatement, safeSqlParameterSource(parameter), rowMapper);
	}

	public <T> List<T> find(String namedStatement, Ordering ordering, RowMapper<T> rowMapper) {
		return this.find(namedStatement, (Object) null, ordering, rowMapper);
	}

	//
	public <T> List<T> find(String namedStatement, String onlyParameterKey, Object onlyParameterValue, RowMapper<T> rowMapper) {
		Map<String, Object> parameter = onlyParameter(onlyParameterKey, onlyParameterValue);
		return this.find(namedStatement, parameter, rowMapper);
	}

	public <T> List<T> find(String namedStatement, SqlParameterSource sqlParameterSource, RowMapper<T> rowMapper) {
		return this.find(namedStatement, sqlParameterSource, null, rowMapper);
	}

	public <T> List<T> find(String namedStatement, Map<String, Object> parameter, RowMapper<T> rowMapper) {
		return this.find(namedStatement, parameter, null, rowMapper);
	}

	public <T> List<T> find(String namedStatement, Object parameter, RowMapper<T> rowMapper) {
		return this.find(namedStatement, parameter, null, rowMapper);
	}

	public <T> List<T> find(String namedStatement, RowMapper<T> rowMapper) {
		return this.find(namedStatement, (Object) null, rowMapper);
	}

	/** List<Map<String, Object>> find */
	public List<Map<String, Object>> find(String namedStatement, String onlyParameterKey, Object onlyParameterValue, Ordering ordering) {
		Map<String, Object> parameter = onlyParameter(onlyParameterKey, onlyParameterValue);
		return this.find(namedStatement, parameter, ordering);
	}

	public List<Map<String, Object>> find(String namedStatement, SqlParameterSource sqlParameterSource, Ordering ordering) {
		return this.find(namedStatement, sqlParameterSource, ordering, new ColumnMapRowMapper());
	}

	public List<Map<String, Object>> find(String namedStatement, Map<String, Object> parameter, Ordering ordering) {
		return this.find(namedStatement, parameter, ordering, new ColumnMapRowMapper());
	}

	public List<Map<String, Object>> find(String namedStatement, Object parameter, Ordering ordering) {
		return this.find(namedStatement, safeSqlParameterSource(parameter), ordering);
	}

	public List<Map<String, Object>> find(String namedStatement, Ordering ordering) {
		return this.find(namedStatement, (Object) null, ordering);
	}

	//
	public List<Map<String, Object>> find(String namedStatement, String onlyParameterKey, Object onlyParameterValue) {
		Map<String, Object> parameter = onlyParameter(onlyParameterKey, onlyParameterValue);
		return this.find(namedStatement, parameter);
	}

	public List<Map<String, Object>> find(String namedStatement, SqlParameterSource sqlParameterSource) {
		return this.find(namedStatement, sqlParameterSource, (Ordering) null);
	}

	public List<Map<String, Object>> find(String namedStatement, Map<String, Object> parameter) {
		return this.find(namedStatement, parameter, (Ordering) null);
	}

	public List<Map<String, Object>> find(String namedStatement, Object parameter) {
		return this.find(namedStatement, parameter, (Ordering) null);
	}

	public List<Map<String, Object>> find(String namedStatement) {
		return this.find(namedStatement, (Ordering) null);
	}

	/** Pagination<T> findPagination */
	public <T> Pagination<T> findPagination(String namedStatement, SqlParameterSource sqlParameterSource, Ordering ordering, Pagination<T> pagination, RowMapper<T> rowMapper) {
		try {
			if (null == pagination) {
				pagination = new Pagination<T>(this.getJdbcTemplate().getMaxRows());
			}
			List<T> items = this.getNamedParameterJdbcTemplate().query(this.jdbcTemplateDialect.paginationStatement(this.jdbcTemplateDialect.appendOrderBy(namedStatement, ordering), pagination),
					sqlParameterSource, rowMapper);
			// if (notEmpty(items)) {
			pagination.setItems(items);
			// }
			if (pagination.isCounted()) {
				String countStatement = this.jdbcTemplateDialect.countStatement(namedStatement);
				int count = this.getNamedParameterJdbcTemplate().queryForInt(countStatement, sqlParameterSource);
				pagination.setCount(count);
			}
			return pagination;
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource + ", ordering=" + ordering + ", pagination=" + pagination + ", rowMapper=" + rowMapper, e);
			throw e;
		}
	}

	public <T> Pagination<T> findPagination(String namedStatement, Map<String, Object> parameter, Ordering ordering, Pagination<T> pagination, RowMapper<T> rowMapper) {
		SqlParameterSource sqlParameterSource = new PaginationSqlParameterSource(parameter, ordering, pagination);
		return this.findPagination(namedStatement, sqlParameterSource, ordering, pagination, rowMapper);
	}

	public <T> Pagination<T> findPagination(String namedStatement, Object parameter, Ordering ordering, Pagination<T> pagination, RowMapper<T> rowMapper) {
		SqlParameterSource sqlParameterSource = new PaginationSqlParameterSource(parameter, ordering, pagination);
		return this.findPagination(namedStatement, sqlParameterSource, ordering, pagination, rowMapper);
	}

	//
	public <T> Pagination<T> findPagination(String namedStatement, SqlParameterSource sqlParameterSource, Pagination<T> pagination, RowMapper<T> rowMapper) {
		return this.findPagination(namedStatement, sqlParameterSource, null, pagination, rowMapper);
	}

	public <T> Pagination<T> findPagination(String namedStatement, Map<String, Object> parameter, Pagination<T> pagination, RowMapper<T> rowMapper) {
		return this.findPagination(namedStatement, parameter, null, pagination, rowMapper);
	}

	public <T> Pagination<T> findPagination(String namedStatement, Object parameter, Pagination<T> pagination, RowMapper<T> rowMapper) {
		return this.findPagination(namedStatement, parameter, null, pagination, rowMapper);
	}

	public <T> Pagination<T> findPagination(String namedStatement, Pagination<T> pagination, RowMapper<T> rowMapper) {
		return this.findPagination(namedStatement, (Object) null, pagination, rowMapper);
	}

	/** count */
	public int count(String namedStatement, SqlParameterSource sqlParameterSource) {
		return this.findInt(namedStatement, new PaginationSqlParameterSource(sqlParameterSource));
	}

	public int count(String namedStatement, Map<String, Object> parameter) {
		return this.findInt(namedStatement, new PaginationSqlParameterSource(parameter));
	}

	public int count(String namedStatement, Object parameter) {
		return this.findInt(namedStatement, new PaginationSqlParameterSource(parameter));
	}

	/** update */
	public int update(String namedStatement, SqlParameterSource sqlParameterSource) {
		try {
			return this.getNamedParameterJdbcTemplate().update(namedStatement, sqlParameterSource);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource, e);

			throw e;
		}
	}

	public int update(String namedStatement, Map<String, Object> parameter) {
		try {
			return this.getNamedParameterJdbcTemplate().update(namedStatement, parameter);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", parameter=" + parameter, e);

			throw e;
		}
	}

	/** remove */
	public int remove(String namedStatement, SqlParameterSource sqlParameterSource) {
		try {
			return this.getNamedParameterJdbcTemplate().update(namedStatement, sqlParameterSource);
		} catch (RuntimeException e) {
			log.error("namedStatement=" + namedStatement + ", sqlParameterSource=" + sqlParameterSource, e);
			throw e;
		}
	}

	public int remove(String namedStatement, Map<String, Object> parameter) {
		return this.remove(namedStatement, new PaginationSqlParameterSource(parameter));

	}

	/** execute */
	public void execute(String sql, SqlParameterSource sqlParameterSource) {

		this.getNamedParameterJdbcTemplate().execute(sql, sqlParameterSource, new PreparedStatementCallback<Object>() {

			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.execute();
				return null;
			}

		});
	}

	public void execute(String sql, Map<String, ?> parameter) {

		this.getNamedParameterJdbcTemplate().execute(sql, parameter, new PreparedStatementCallback<Object>() {

			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.execute();
				return null;
			}

		});
	}

	public Map<String, ?> execute(StoredProcedureParam storedProcedureParam, Map<String, Object> parameter) {
		try {
			SpringStoredProcedure storedProcedure = new SpringStoredProcedure(this.getJdbcTemplate(), storedProcedureParam.getCallString());
			if (null != storedProcedureParam.getDeclareParameters()) {
				List<SqlParameter> declareParameters = storedProcedureParam.getDeclareParameters();
				for (SqlParameter sqlParameter : declareParameters) {
					if (java.sql.Types.CLOB == sqlParameter.getSqlType() || java.sql.Types.BLOB == sqlParameter.getSqlType()) {
						String key = sqlParameter.getName();
						Object object = parameter.get(key);
						if (object instanceof String) {
							String value = (String) object;
							parameter.remove(key);
							parameter.put(key, new SqlLobValue(value, lobHandler));
						}
					}
					storedProcedure.declareParameter(sqlParameter);
				}
			}
			return storedProcedure.execute(parameter);
		} catch (RuntimeException e) {
			log.error("storedProcedureParam=" + storedProcedureParam + ", parameter=" + parameter, e);
			throw e;
		}
	}

	/**
	 * @return Returns the namedParameterJdbcTemplate.
	 */
	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	/**
	 * @param namedParameterJdbcTemplate
	 *            The namedParameterJdbcTemplate to set.
	 */
	public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}

	/**
	 * @return the lobHandler
	 */
	public LobHandler getLobHandler() {
		return lobHandler;
	}

	/**
	 * @param lobHandler
	 *            the lobHandler to set
	 */
	public void setLobHandler(LobHandler lobHandler) {
		this.lobHandler = lobHandler;
	}

	/**
	 * @return the jdbcTemplateDialect
	 */
	public JdbcTemplateDialect getJdbcTemplateDialect() {
		return jdbcTemplateDialect;
	}

	/**
	 * @param jdbcTemplateDialect
	 *            the jdbcTemplateDialect to set
	 */
	public void setJdbcTemplateDialect(JdbcTemplateDialect jdbcTemplateDialect) {
		this.jdbcTemplateDialect = jdbcTemplateDialect;
	}

}
